
package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Thomas
 */
public class DBInvoice implements IFDBInvoice 
{
    private Connection con;
    
    
    /**
     * Creates a new instance of DBInvoice
     * @return 
     */
     public DBInvoice() 
     {
         con = DbConnection1.getInstance().getDBcon();
     }
    
     
    //Implements the methods from the interface
    //get all invoices
    @Override
    public ArrayList<Invoice> getAllInvoices()
    {
        return miscWhere("");
    }
    
    //get one invoice having the id
    @Override
    public Invoice findInvoice(int invoiceNo)
    {
        String wClause = "  invoiceNo = '" + invoiceNo + "'";
        return singleWhere(wClause);
        
    }
    //find all invoices created on a given date
    @Override
    public ArrayList<Invoice> searchInvoiceDate(String date)
    {
        String wClause = "  date = '" + date+"'";
        return miscWhere(wClause); 
    }
    
    //finds a invoice with a given amoun
    @Override
    public Invoice findInvoiceWAmount(double amount)
    {
        String wClause = " amount = '"+ amount + "'";
        return singleWhere(wClause);
    }
    
    //insert a new invoice
    @Override
    public int insertInvoice(Invoice invoice)
    {
        int rc = -1;
       
        //call to get the next invoiceNo
        int nextInvoiceNo = GetMax.getMaxId("Select max(invoiceNo) from Invoice");
        nextInvoiceNo = nextInvoiceNo + 1;
        
        String  query="INSERT INTO Invoice(invoiceNo, paymentDate, amount, date)" + "VALUES('"+
                   nextInvoiceNo  + "','"  +
                   invoice.getPaymentDate() + "','"  +
                   invoice.getAmount() + "','"  +
                   invoice.getDate() + "')";
                   
        
        System.out.println(query);
	  	try{ // insert to person
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//end try	
   	        catch(Exception ex)
                {
	 	  	System.out.println("Insert exception in invoice db: "+ex);
   	        }
                     
	   return(rc);
    }
    //update information about an invoice
    @Override
    public int updateInvoice(Invoice invoice)
    {
		int rc=-1;

		String query="UPDATE Invoice SET "+
                          
		 	  "paymentDate ='"+ invoice.getPaymentDate()+"', "+
		 	  "amount ='"+ invoice.getAmount() + "', " +
                          "date ='"+ invoice.getDate()+"'"  +
                          
		          " WHERE invoiceNo = '"+ invoice.getInvoiceNo() + "'";
                        
                System.out.println("Update query:" + query);
  		try
                { // update invoice
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in invoice db: "+ex);
	  	}
		return(rc);
        
        
    }
    //delete a invoice
    @Override
    public int deleteInvoice(int invoiceNo)
    {
        int rc=-1;
        String query="DELETE FROM Invoice WHERE invoiceNo = '" + invoiceNo + "'";
        System.out.println(query);
	try
        {
            // delete from invoice
	    Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
       	}//End try
        
   	catch(Exception ex)
        {
            System.out.println("Delete exception in Invoice db: "+ex);
        }
        
        return(rc);
        
    }
    
    //private methods
    //michWere is used whenever we want to select more than one invoice
    private ArrayList<Invoice> miscWhere(String wClause)
	{
            ResultSet results;
	    ArrayList<Invoice> list = new ArrayList<Invoice>();	
		
	    String query =  buildQuery(wClause);
  
            try
            { 
                //read the invoice from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
		int snr=0;
		while( results.next() )
                {
                    Invoice inv = new Invoice();
                    inv = buildInvoice(results);	
                    list.add(inv);
                }//end while
                
                stmt.close();
            }//slut try
            
            catch(Exception e)
            {
                System.out.println("Query exception - select: "+e);
			e.printStackTrace();
            }
            
            return list;	
	}
    
    //Singelwhere is used when we only select one invoice 	
    private Invoice singleWhere(String wClause)
	{
            ResultSet results;
	    Invoice inv = new Invoice();
                
	    String query =  buildQuery(wClause);
            System.out.println(query);
            try
            { // read the invoice from the database
	 	Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 		
	 	if( results.next() )
                {
                    inv = buildInvoice(results);
                    stmt.close();
                }
                
                else
                {
                    //no invoice was found
                    inv = null;
                }
            }//end try
            
            catch(Exception e)
            {
                System.out.println("Query exception: "+e);
            }
            
            return inv;
        }
    
    //method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT invoiceNo, paymentDate, amount, date FROM Invoice";
		
		if (wClause.length()>0)
		query=query+" WHERE "+ wClause;
			
		return query;
	}
        
        //method to build a invoice order object
	private Invoice buildInvoice(ResultSet results)
        {   
            Invoice invoiceObj = new Invoice();

            try
            {  
                invoiceObj.setInvoiceNo(results.getInt("invoiceNo"));
                invoiceObj.setPaymentDate(results.getString("paymentDate"));
                invoiceObj.setAmount(results.getDouble("amount"));
                invoiceObj.setDate(results.getString("date"));
            }
            
            catch(Exception e)
            {
                System.out.println("error in building the sales order object");
            }
            return invoiceObj;
        }
        
        public int getMaxId()
        {
            int maxInvoiceId = GetMax.getMaxId("Select max(invoiceNo) from Invoice");
            return maxInvoiceId;
        }
   
}
